﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;
using System.Data.SqlClient;

namespace BLL
{
    public class THANHVIENBLL
    {
        DataService DB = new DataService();

        //get all values
        public List<THANHVIEN> getALL() {
            string sql = "select * from thanhvien";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

             List<THANHVIEN> lstTv = new List<THANHVIEN>();
            foreach(DataRow r in table.Rows){
                THANHVIEN tv = new THANHVIEN();
                tv.mathanhvien = (int)r[0];
                tv.tenthanhvien = (string)r[1];
                try { tv.chucvu = r[2].ToString(); }
                catch { tv.chucvu = ""; }
                try { tv.email = r[3].ToString(); }
                catch { tv.email = ""; }
                try { tv.hinh = r[4].ToString(); }
                catch { tv.hinh = ""; }

                lstTv.Add(tv);
            }
            return lstTv;
        }

        //insert value
        public void insert(string tenthanhvien, string chucvu
            , string email, string hinh) {

            string sql = "insert into thanhvien values ( @tenthanhvien, @chucvu, @email, @hinh)";
            SqlParameter paraTv = new SqlParameter("tenthanhvien", tenthanhvien);
            SqlParameter paraCv = new SqlParameter("chucvu", chucvu);
            SqlParameter paraEm = new SqlParameter("email", email);
            SqlParameter paraHi = new SqlParameter("hinh", hinh);
                
            DB.ExecuteNonQuery(sql, paraTv, paraCv, paraEm, paraHi);

        }

        public void update(int mathanhvien, string tenthanhvien, string chucvu, string email, string hinh)
        {
            string sql = "update thanhvien set tenthanhvien = N'" + tenthanhvien + "' ,chucvu = N'" + chucvu + "' , email = '" + email + "' , hinh = ' " + hinh + "' where mathanhvien = " + mathanhvien.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int mathanhvien)
        {
            string sql = "delete from thanhvien where mathanhvien = " + mathanhvien.ToString();
            DB.ExecuteNonQuery(sql);
        }

        //get count of thanhvien
        public int count() {
            string sql = "select count(*) from thanhvien";
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        public THANHVIEN getThanhvienWithTenTK(string tentaikhoan) {
            string sql = "select tv.mathanhvien,tenthanhvien,chucvu,email,hinh from thanhvien tv,taikhoan tk "
            + "where tv.mathanhvien = tk.mathanhvien and tk.tentaikhoan = '" + tentaikhoan + "'";

            DataTable table = new DataTable();
            if (DB.ExecuteReader(sql) == null) return null;
            else table = DB.ExecuteReader(sql);

            DataRow r = table.Rows[0];
            THANHVIEN tv = new THANHVIEN();
            tv.mathanhvien = (int)r[0];
            tv.tenthanhvien = (string)r[1];
            try { tv.chucvu = r[2].ToString(); }
            catch { tv.chucvu = ""; }
            try { tv.email = r[3].ToString(); }
            catch { tv.email = ""; }
            try { tv.hinh = r[4].ToString(); }
            catch { tv.hinh = ""; }
            return tv;

        }

        public THANHVIEN getThanhvienWithIDBaiviet(int idBaiviet) {
            string sql = "select tv.mathanhvien,tv.tenthanhvien,tv.chucvu,tv.email,tv.hinh "
                        + " from thanhvien tv,baiviet bv where tv.mathanhvien = bv.mathanhvien "
                        + " and	bv.mabaiviet = '" + idBaiviet.ToString() + "'";

            DataTable table = new DataTable();
            if (DB.ExecuteReader(sql) == null) return null;
            else table = DB.ExecuteReader(sql);

            DataRow r = table.Rows[0];
            THANHVIEN tv = new THANHVIEN();
            tv.mathanhvien = (int)r[0];
            tv.tenthanhvien = (string)r[1];
            try { tv.chucvu = r[2].ToString(); }
            catch { tv.chucvu = ""; }
            try { tv.email = r[3].ToString(); }
            catch { tv.email = ""; }
            try { tv.hinh = r[4].ToString(); }
            catch { tv.hinh = ""; }
            return tv;
        }

        public void Update_email(string email , string tentaikhoan)
        {
            string sql = "update thanhvien set email = @email from taikhoan tk,thanhvien tv where tv.mathanhvien = tk.mathanhvien and tk.tentaikhoan = '" + tentaikhoan +"'";
            SqlParameter paraEm = new SqlParameter("email", email);
            DB.ExecuteNonQuery(sql, paraEm);
        }
        public DataTable getallThanhvien()
        {
            string sql = "select * from thanhvien";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);
            return table;
        }
        public THANHVIEN getThanhvienWithIDComment(int idComment) {
            string sql = "select tv.mathanhvien,tv.tenthanhvien,tv.chucvu,tv.email,tv.hinh from comment cme, thanhvien tv"
                        + " where cme.mathanhvien = tv.mathanhvien and cme.macomment = " + idComment.ToString();

            DataTable table = new DataTable();
            if (DB.ExecuteReader(sql) == null) return null;
            else table = DB.ExecuteReader(sql);

            DataRow r = table.Rows[0];
            THANHVIEN tv = new THANHVIEN();
            tv.mathanhvien = (int)r[0];
            tv.tenthanhvien = (string)r[1];
            try { tv.chucvu = r[2].ToString(); }
            catch { tv.chucvu = ""; }
            try { tv.email = r[3].ToString(); }
            catch { tv.email = ""; }
            try { tv.hinh = r[4].ToString(); }
            catch { tv.hinh = ""; }
            return tv;
        }

        public void updateHinhWithIdThanhvien(int idThanhvien, string hinh) {
            string sql = "update thanhvien set hinh = '" + hinh + "' where mathanhvien = " + idThanhvien.ToString();
            DB.ExecuteScalar(sql);
        }

        public THANHVIEN getThanhvienWithIDThanhvien(int idThanhvien) {
            string sql = "select * from thanhvien where mathanhvien =" + idThanhvien.ToString();

            DataTable table = new DataTable();
            if (DB.ExecuteReader(sql) == null) return null;
            else table = DB.ExecuteReader(sql);

            DataRow r = table.Rows[0];
            THANHVIEN tv = new THANHVIEN();
            tv.mathanhvien = (int)r[0];
            tv.tenthanhvien = (string)r[1];
            try { tv.chucvu = r[2].ToString(); }
            catch { tv.chucvu = ""; }
            try { tv.email = r[3].ToString(); }
            catch { tv.email = ""; }
            try { tv.hinh = r[4].ToString(); }
            catch { tv.hinh = ""; }
            return tv;
        }


        public void insertFast(string tenthanhvien,string chucvu,string email) {
            string sql = "insert into thanhvien values (N'" + tenthanhvien + "',N'" + chucvu + "','" + email + "',null)";
            DB.ExecuteNonQuery(sql);  
        }

        public THANHVIEN getBestThanhvien() {
            string sql = "select top 1 * from thanhvien order by mathanhvien desc";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);
            
            DataRow r = table.Rows[0];
                THANHVIEN tv = new THANHVIEN();
                tv.mathanhvien = Int32.Parse(r[0].ToString());
                tv.tenthanhvien = r[1].ToString();
                try { tv.chucvu = r[2].ToString(); }
                catch { tv.chucvu = ""; }
                try { tv.email = r[3].ToString(); }
                catch { tv.email = ""; }
                try { tv.hinh = r[4].ToString(); }
                catch { tv.hinh = ""; }

            return tv;
        }

        public THANHVIEN getThanhvienWithEmail(string email) {
            string sql = "select * from thanhvien where email = '" + email + "'";

            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            DataRow r = table.Rows[0];
            THANHVIEN tv = new THANHVIEN();
            tv.mathanhvien = Int32.Parse(r[0].ToString());
            tv.tenthanhvien = r[1].ToString();
            try { tv.chucvu = r[2].ToString(); }
            catch { tv.chucvu = ""; }
            try { tv.email = r[3].ToString(); }
            catch { tv.email = ""; }
            try { tv.hinh = r[4].ToString(); }
            catch { tv.hinh = ""; }

            return tv;
        }
    }
}
